
package eti.vilas;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import org.sqlite.Function;

public class DBConnection
{

   private Connection conn;
   private Statement stm;

   /**
    * O construtor cria uma nova conexão com o banco de dados sqlite contido no arquivo passado como parâmetro. A conexão é
    * possibilitada pelo driver JDBC, fornecido por SQLiteJDBC.
    */
   public DBConnection(String file) throws SQLException, ClassNotFoundException
   {
      Class.forName("org.sqlite.JDBC");
      this.conn = DriverManager.getConnection("jdbc:sqlite:" + file);
      this.stm = this.conn.createStatement();
   }

   /**
    * Cria uma nova tabela de abastecimento, apagando tudo que houvesse na base anteriormente.
    */
   public void initDB()
   {
      try
      {
         this.stm.executeUpdate("DROP TABLE IF EXISTS abastecimento;");
         this.stm.executeUpdate("CREATE TABLE abastecimento ( " + 
                                  "placa       VARCHAR( 7 )   NOT NULL, " +
                                  "cnh         INTEGER( 11 )  NOT NULL, " +
                                  "data        DATETIME, " +
                                  "vr_litro    FLOAT( 6, 3 ), " +
                                  "vr_total    FLOAT( 6, 3 ), "+
                                  "km_start    INTEGER( 10 ), "+
                                  "km_end      INTEGER( 10 ), "+
                                  "litros      INTEGER( 5 ), "+
                                  "combustivel VARCHAR( 25 ), "+
                                  "FOREIGN KEY ( placa ) REFERENCES veiculo ( placa ), " +
                                  "FOREIGN KEY ( cnh ) REFERENCES condutor ( cnh ) "+
                                  ");");
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }

   /**
    * Adiciona uma nova linha na tabela de recordes.
    * 
    * @param score
    */
   public int addAbst(Abast abast)
   {
      try
      {
         this.stm = this.conn.createStatement();
         return this.stm.executeUpdate(abast.sqlInsert());
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
      
      return -1;
   }

   /**
    * Remove a linha da tabela cuja coluna "jogo" seja igual a string passada como parâmetro.
    * 
    * @param jogo
    */
   public int removeAbast(Abast abast)
   {
      try
      {
         this.stm = this.conn.createStatement();
         return this.stm.executeUpdate(abast.sqlDelete());
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
      return -1;
   }

   /**
    * Altera o valor de uma linha do banco de dados.
    * 
    * @param hiScore
    */
   public int atualizaAbast(Abast abast)
   {
      try
      {
         this.stm = this.conn.createStatement();
         return this.stm.executeUpdate(abast.sqlUpdate());
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
      return 0;
   }

   /**
    * Retorna um objeto Vector com todos os recordes do banco de dados organizados em ordem decrescente de pontuação.
    * 
    * @return
    * @throws SQLException
    */
   public Vector<Abast> getAll()
   {
      Vector<Abast> abastecimentos = new Vector<Abast>();
      ResultSet rs;
      try
      {

         rs = this.stm.executeQuery(Abast.sqlSelect());
         while (rs.next())
         {
            // System.out.println(rs.getString("conc")); // Teste
            Abast abast = new Abast();
            abast.setId(rs.getInt("rowid"));
            abast.setCnh(rs.getInt("cnh"));
            abast.setPlaca(rs.getString("placa"));
            abast.setData(rs.getString("data"));
            abast.setVrLitro(rs.getFloat("vr_litro"));
            abast.setVrTotal(rs.getFloat("vr_total"));
            abast.setKmStart(rs.getInt("km_start"));
            abast.setKmEnd(rs.getInt("km_end"));
            abast.setLitros(rs.getInt("litros"));
            abast.setCombustivel(rs.getString("combustivel"));

            abastecimentos.add(abast);
         }
         rs.close();
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }

      return abastecimentos;
   }

   public Abast getUltimoAbast()
   {
      Abast abast = null;
      ResultSet rs;
      try
      {
         rs = this.stm.executeQuery(Abast.sqlSelectLast());
         while (rs.next())
         {
            abast = new Abast();
            abast.setId(rs.getInt("rowid"));
            abast.setCnh(rs.getInt("cnh"));
            abast.setPlaca(rs.getString("placa"));
            abast.setData(rs.getString("data"));
            abast.setVrLitro(rs.getFloat("vr_litro"));
            abast.setVrTotal(rs.getFloat("vr_total"));
            abast.setKmStart(rs.getInt("km_start"));
            abast.setKmEnd(rs.getInt("km_end"));
            abast.setLitros(rs.getInt("litros"));
            abast.setCombustivel(rs.getString("combustivel"));
         }
         rs.close();
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
      return abast;
   }
   
   public Item getByComgustivel(Combustivel combustivel)
   {
      Item item = new Item();
      ResultSet rs;
      try
      {
         rs = this.stm.executeQuery(Item.sqlSelectByCombustivel(combustivel.toString())); 
         while (rs.next())
         {
            item.setCombustivel(rs.getString("combustivel"));
            item.setKm(rs.getInt("kms"));
            item.setLitros(rs.getInt("litros"));
            item.setTotal(rs.getDouble("total"));
         }
         rs.close();
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
      return item;
   }
   
   public List<Item> getGroupByComgustivel()
   {
      List<Item> itens = new ArrayList<Item>();
      ResultSet rs;
      try
      {
         rs = this.stm.executeQuery(Item.sqlSelectGroupByCombustivel()); 
         while (rs.next())
         {
            Item item = new Item();
            item.setCombustivel(rs.getString("combustivel"));
            item.setKm(rs.getInt("kms"));
            item.setLitros(rs.getInt("litros"));
            item.setTotal(rs.getDouble("total"));
            itens.add(item);
         }
         rs.close();
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
      return itens;
   }
   
   public void printAbastTable()
   {
      ResultSet rs = null;
      try
      {
         rs = this.stm.executeQuery(Abast.sqlSelectByConsumo());
         
         ResultSetMetaData meta = rs.getMetaData();
        
         //int tCol[] = calculaMaiorTamanho(rs);
         
         for (int i = 1; i <= meta.getColumnCount(); i++)
         {
            System.out.printf("%-20s", meta.getColumnName(i));
         }
         System.out.println();
         
         while (rs.next())
         {
            
            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++)
            {
               System.out.printf("%-20s", rs.getString(i));
            }

            System.out.println();
            
         }
         
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
      finally
      {
            try
            {
               if(rs != null)
               rs.close();
            }
            catch (SQLException e)
            {
               e.printStackTrace();
            }
      }
   }

   private int[] calculaMaiorTamanho(ResultSet rs) throws SQLException
   {

      ResultSetMetaData meta = rs.getMetaData();
      int max[] = new int[meta.getColumnCount()];

      for (int j = 1; j <= max.length; j++)
      {

         while (rs.next())
         {
            if(rs.getString(j).length() > max[j] )
               max[j] = rs.getString(j).length();
         }
      }

      return max;
   }

   public void close() throws SQLException
   {
      conn.close();
      stm.close();
   }
}
